/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import AirLine.Airport;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.TreeSet;

/**
 *
 * @author smulle
 */
public class Airports extends DBObject {


    /*
     * ORACLE_033
     */
    private static final String ORACLE_033_CODE_COLUMN = "CODE";
    private static final String ORACLE_033_NAME_COLUMN = "NAME";
    private static final String ORACLE_033_AIRPORT_TABLE_NAME = "AIRPORT";
    /*
     * ORACLE_013
     */
    private static final String ORACLE_013_CODE_COLUMN = "AIRPORT_ID";
    private static final String ORACLE_013_NAME_COLUMN = "NAME";
    private static final String ORACLE_013_AIRPORT_TABLE_NAME = "AIRPORT";
    /*
     * MySQL
     */
    private static final String MYSQL_CODE_COLUMN = "ID";
    private static final String MYSQL_NAME_COLUMN = "NAME";
    private static final String MYSQL_AIRPORT_TABLE_NAME = "Airport";
    private TreeSet<Airport> airportsList = null;
    private Hashtable<String, Airport> airports = null;

    public Airports() {

    }

    public void update() throws SQLException {
        connect();
        airportsList = new TreeSet<Airport>();
        airports = new Hashtable<String, Airport>();
        Airport airport;

        //ORACLE 033
        PreparedStatement oracle_033_pstmnt = getConnection(ORACLE_033).prepareStatement(SELECT + ORACLE_033_CODE_COLUMN + SEPARATOR + ORACLE_033_NAME_COLUMN + FROM + ORACLE_033_AIRPORT_TABLE_NAME);
        ResultSet resultset_oracle033 = oracle_033_pstmnt.executeQuery();
        while (resultset_oracle033.next()) {

            String name = resultset_oracle033.getString(ORACLE_033_NAME_COLUMN);
            String code = resultset_oracle033.getString(ORACLE_033_CODE_COLUMN);
            airport = new Airport(name, code);
//            System.out.println(airportsList.contains(airport));
            addAirportToList(airport);

        }

        // To get the Airports from "dat135_013" oracle database        
        PreparedStatement oracle_013_pstmnt = getConnection(ORACLE_013).prepareStatement(SELECT + ORACLE_013_CODE_COLUMN + SEPARATOR + ORACLE_013_NAME_COLUMN + FROM + ORACLE_013_AIRPORT_TABLE_NAME);
        ResultSet resultset_oracle013 = oracle_013_pstmnt.executeQuery();

        while (resultset_oracle013.next()) {
            String name = resultset_oracle013.getString(ORACLE_013_NAME_COLUMN);
            String code = resultset_oracle013.getString(ORACLE_013_CODE_COLUMN);
            //System.out.println(name + "--" + code);
            airport = new Airport(name, code);
            addAirportToList(airport);
        }

        //To get the airports from mysql 013
        PreparedStatement mysql_pstmnt = getConnection(MYSQL_013).prepareStatement(SELECT + MYSQL_CODE_COLUMN + SEPARATOR + MYSQL_NAME_COLUMN + FROM + MYSQL_AIRPORT_TABLE_NAME);
        ResultSet resultset_mysql = mysql_pstmnt.executeQuery();
        while (resultset_mysql.next()) {
            String name = resultset_mysql.getString(MYSQL_NAME_COLUMN);
            String code = resultset_mysql.getString(MYSQL_CODE_COLUMN);
            airport = new Airport(name, code);
            addAirportToList(airport);

        }

        //MYSQL

        commit();
        resultset_mysql.close();
        resultset_oracle013.close();
        resultset_oracle033.close();
        mysql_pstmnt.close();
        oracle_013_pstmnt.close();
        oracle_033_pstmnt.close();
        closeConnections();
    }

    public void populate() throws SQLException {
        if (airports == null || airportsList == null) {
            update();
        }

    }

    /*
     * Fast
     */
    public Airport getAirportByCode(String code) throws SQLException {
        if (airports == null) {
            populate();
        }



        return airports.get(code);
    }

    /*
     * Slow
     */
    public Airport getAirportByName(String name) throws SQLException {
        if (airportsList == null) {
            populate();
        }

        Iterator it = airportsList.iterator();
        Airport retval;
        while (it.hasNext()) {
            retval = (Airport) it.next();
            if (retval.getAirportName().equals(name)) {
                return retval;
            }
        }
        return null;
    }

    public Hashtable<String, Airport> getAirports() throws SQLException {
        if (airports == null) {
            populate();
        }
        return airports;
    }

    public TreeSet<Airport> getAirportsList() throws SQLException {
        if (airportsList == null) {
            populate();
        }
        return airportsList;
    }

    private void addAirportToList(Airport a) {
        if (!airports.containsKey(a.getAirportCode())) {
            airports.put(a.getAirportCode(), a);
            airportsList.add(a);
        }

    }
}


